﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace NG
{
    class ProjectDepartmentRoles:IProjectDepartmentRoles
    {
        private string tableName = "[dbo].[ProjectDepartmentRoles]";
        private int m_intervieweeNo;

        public void addProjectDepartmentRole(Project pro, SubDepartment sdep, Roles role)
        {
            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;
            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }
            string query = "INSERT INTO " + tbn + " (project,department,subDepartment,role) VALUES (" + pro.ID + "," + sdep.DepCode +
                "," + sdep.SubDepCode + "," + role.roleNum + ");"
             + "SELECT intervieweeNo FROM " +tbn+ " WHERE intervieweeNo = @@IDENTITY;";

       

            SqlCommand cmd = new SqlCommand(query, con.connection);
            this.m_intervieweeNo = int.Parse(cmd.ExecuteScalar().ToString());

            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }
        }

        public LinkedList<int> allIntervieweeNums(string sortedBy, bool desc,Project pro,Roles role )
        {
            string sorted;
            if (sortedBy == string.Empty)
            {
                sorted = " {0}";
            }
            else
            {
                sorted = sortedBy;
            }
            DBConnect con = new DBConnect();
            string tbn = "[" + con.dbName + "]." + this.tableName;
            LinkedList<int> intervieweeList = new LinkedList<int>();
            if (con.OpenConnection() == false)
            {
                throw new System.Exception("Couldn't connect to the Database");
            }
            string extra = "";
            if (desc)
            {
                extra = " DESC";
            }

            string query = "SELECT intervieweeNo FROM"  + tbn + "WHERE project= " + pro.ID +"AND role=" + role.roleNum + "ORDER BY " + sorted + extra;
            SqlCommand cmd = new SqlCommand(query, con.connection);
            SqlDataReader rdr = cmd.ExecuteReader();


            if (rdr.HasRows)
            {

                while (rdr.Read())
                {
                    intervieweeList.AddLast((int.Parse(rdr[0].ToString())));
                }
            }
            if (con.CloseConnection() == false)
            {
                throw new System.Exception("Couldn't disconnect from the Database");
            }
            return intervieweeList;
        }
       
        public void addProjectDepartmentRole(int key)
        {
        }
    }
}
